package com.qxsx.midi.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import com.qxsx.midi.model.MyFile;

import java.util.ArrayList;
import java.util.List;


public class MyFileDBTool {
	private SQLiteDatabase db ;
	private Context context ;
	public MyFileDBTool(Context context) {
		this.context = context ;
	}	
	
	private void connDB() {
		if(null == db) {
			db = new MyOpenHelper(context).getWritableDatabase() ;
		}
	}

	private void closeDB() {
		if(null != db) {
			db.close() ;
			db = null ;
		}
	}


	public void insertOne(MyFile file) {
		connDB() ;

		ContentValues values = new ContentValues() ;
		values.put(MyOpenHelper.COLUMN_MOVIE_TITLE , file.getFileName()) ;		
		values.put(MyOpenHelper.COLUMN_MOVIE_SIZE, file.getFileSize());
		values.put(MyOpenHelper.COLUMN_MOVIE_LASTMODIFIED, file.getFileTime());
		values.put(MyOpenHelper.COLUMN_MOVIE_PATH , file.getFilePath()) ;
		values.put(MyOpenHelper.COLUMN_MOVIE_IMAGE , file.getFileImage()) ;
		values.put(MyOpenHelper.COLUMN_MOVIE_THUMBNAIL , file.getIsHaveThumbnail()) ;
		db.insert(MyOpenHelper.TABLE_NAME, null, values) ;  
		
		closeDB() ;
	}
	
	public void insertOne1(MyFile file) {
		connDB() ;

		String sql = "INSERT INTO " + MyOpenHelper.TABLE_NAME +"("+MyOpenHelper.COLUMN_MOVIE_TITLE+","+
				MyOpenHelper.COLUMN_MOVIE_SIZE+","+MyOpenHelper.COLUMN_MOVIE_LASTMODIFIED+","+MyOpenHelper.COLUMN_MOVIE_PATH
				+","+MyOpenHelper.COLUMN_MOVIE_IMAGE+","+MyOpenHelper.COLUMN_MOVIE_THUMBNAIL+")" + "VALUES(?,?,?,?,?,?)";
		db.beginTransaction();
		
		SQLiteStatement stmt = db.compileStatement(sql); 
//		ContentValues values = new ContentValues() ;
//		values.put(MyOpenHelper.COLUMN_MOVIE_TITLE , file.getFileName()) ;		
//		values.put(MyOpenHelper.COLUMN_MOVIE_SIZE, file.getFileSize());
//		values.put(MyOpenHelper.COLUMN_MOVIE_LASTMODIFIED, file.getFileTime());
//		values.put(MyOpenHelper.COLUMN_MOVIE_PATH , file.getFilePath()) ;
//		values.put(MyOpenHelper.COLUMN_MOVIE_IMAGE , file.getFileImage()) ;
//		values.put(MyOpenHelper.COLUMN_MOVIE_THUMBNAIL , file.getIsHaveThumbnail()) ;
		
		stmt.bindString(1, file.getFileName());
		stmt.bindString(2, file.getFileSize());
		stmt.bindString(3, file.getFileTime());
		stmt.bindString(4, file.getFilePath());
		stmt.bindBlob(5, file.getFileImage());
		stmt.bindLong(6, file.getIsHaveThumbnail());
		
		
		    stmt.execute();  
		    stmt.clearBindings();  
		
		

//		db.insert(MyOpenHelper.TABLE_NAME, null, values) ;  
		
		db.setTransactionSuccessful();
		db.endTransaction();
		closeDB() ;
	}
	
	public void insertOne2(List<MyFile> file) {
		connDB() ;
		
		
		String sql = "INSERT INTO " + MyOpenHelper.TABLE_NAME +"("+MyOpenHelper.COLUMN_MOVIE_TITLE+","+
				MyOpenHelper.COLUMN_MOVIE_SIZE+","+MyOpenHelper.COLUMN_MOVIE_LASTMODIFIED+","+MyOpenHelper.COLUMN_MOVIE_PATH
				+","+MyOpenHelper.COLUMN_MOVIE_IMAGE+","+MyOpenHelper.COLUMN_MOVIE_THUMBNAIL+")" + "VALUES(?,?,?,?,?,?)";
		db.beginTransaction();
		
//		for (int i = 0; i < file.size(); i++) {
//			
//		}
		SQLiteStatement stmt = db.compileStatement(sql); 
		for (MyFile file2:file) {
			
			stmt.bindString(1, file2.getFileName());
			stmt.bindString(2, file2.getFileSize());
			stmt.bindString(3, file2.getFileTime());
			stmt.bindString(4, file2.getFilePath());
			stmt.bindBlob(5, file2.getFileImage());
			stmt.bindLong(6, file2.getIsHaveThumbnail());
		}
		
		

		
		
		stmt.execute();  
		stmt.clearBindings();  
		
		
		
//		db.insert(MyOpenHelper.TABLE_NAME, null, values) ;  
		
		db.setTransactionSuccessful();
		db.endTransaction();
		closeDB() ;
	}


	public List<MyFile> getAll() {
		connDB() ;
		String sql = "SELECT * FROM " + MyOpenHelper.TABLE_NAME ;//+" ORDER BY " + MyOpenHelper.COLUMN_MOVIE_TITLE;
		Cursor cursor = db.rawQuery(sql, null) ;
		List<MyFile> data = new ArrayList<MyFile>() ;
		while(cursor.moveToNext()) {
			MyFile myFile = new MyFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),cursor.getBlob(5),cursor.getInt(6)) ;
			data.add(myFile) ;
		}
		cursor.close() ;
		closeDB() ;
		return data ;
	}
	

	public MyFile getOne(long id) {
		connDB() ;
		String sql = "SELECT * FROM " + MyOpenHelper.TABLE_NAME + " WHERE " + MyOpenHelper.COLUMN_ID + "=?" ;
		Cursor cursor = db.rawQuery(sql, new String[]{id + ""}) ;
		MyFile myFile = null ;
		if(cursor.moveToNext()) {
			myFile = new MyFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),cursor.getBlob(5),cursor.getInt(6)) ;
		}
		closeDB() ;
		return myFile ;
	}
	
	
	public Boolean getOne(String fileName) {
		boolean flag = false;
		connDB() ;
		String sql = "SELECT * FROM " + MyOpenHelper.TABLE_NAME + " WHERE " + MyOpenHelper.COLUMN_MOVIE_TITLE + "=?" ;
		Cursor cursor = db.rawQuery(sql, new String[]{fileName + ""}) ;
		@SuppressWarnings("unused")
		MyFile myFile = null ;
		if(cursor.moveToNext()) {
			myFile = new MyFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),cursor.getBlob(5),cursor.getInt(6)) ;
			flag = true;
		}
		closeDB() ;
		return flag ;
	}
	public Boolean getOne1(String fileName) {
		boolean flag = false;
		connDB() ;
		String sql = "SELECT * FROM " + MyOpenHelper.TABLE_NAME + " WHERE " + MyOpenHelper.COLUMN_MOVIE_TITLE + "=?" ;
		Cursor cursor = db.rawQuery(sql, new String[]{fileName + ""}) ;
		@SuppressWarnings("unused")
		MyFile myFile = null ;
		if(cursor.moveToNext()) {
			myFile = new MyFile(cursor.getLong(0), cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4),cursor.getBlob(5),cursor.getInt(6)) ;
			flag = true;
		}
		closeDB() ;
		return flag ;
	}

	public void deleteOne(long id) {
		connDB() ;
		String sql = "DELETE FROM " + MyOpenHelper.TABLE_NAME + " WHERE " + MyOpenHelper.COLUMN_ID + "=?" ;
		db.execSQL(sql, new Object[]{id}) ;
		closeDB() ;
	}
	
	public void deleteAll(){
		connDB();
		String sql = "DELETE FROM " + MyOpenHelper.TABLE_NAME;
		db.execSQL(sql) ;
		closeDB();
	}

	public void updateOne(long id , MyFile file) {
		connDB() ;
		ContentValues values = new ContentValues() ;
		values.put(MyOpenHelper.COLUMN_MOVIE_TITLE, file.getFileName()) ;
		values.put(MyOpenHelper.COLUMN_MOVIE_SIZE, file.getFileSize());
		values.put(MyOpenHelper.COLUMN_MOVIE_LASTMODIFIED, file.getFileTime());
		values.put(MyOpenHelper.COLUMN_MOVIE_PATH, file.getFilePath()) ;
		values.put(MyOpenHelper.COLUMN_MOVIE_IMAGE, file.getFileImage()) ;
		values.put(MyOpenHelper.COLUMN_MOVIE_THUMBNAIL, file.getIsHaveThumbnail()) ;
		db.update(MyOpenHelper.TABLE_NAME, values, MyOpenHelper.COLUMN_ID + "=?", new String[]{id+""}) ;
		closeDB() ;
	}
		
}













